query to get statistic data from SQL server-CodePudding

i have a table named total sales. In this table there are sales data like invoice date and branch name that sells the invoice and the quantity.

I am trying to make a query to get total sales for each branch in every single date and my code is below but when I execute the code the query return the an error and I can not to determined the condition for every column.

my code :

SELECT invoice_date,
       COUNT(quantity) AS malqaStore,
       COUNT(quantity) AS tahliaStore
FROM total_sales
WHERE branche_name = 'branch1'
  AND branche_name = 'branch2'
GROUP BY invoice_date;

CodePudding user response：

Based on your description, what you wanted should be

SELECT invoice_date,
       SUM(case when branche_name  = 'branch1' then quantity end) AS malqaStore,
       SUM(case when branche_name  = 'branch2' then quantity end) AS tahliaStore
FROM  total_sales
WHERE branche_name in ( 'branch1' , 'branch2' )
GROUP BY invoice_date;

You can use branche_name in ( 'branch1' , 'branch2' ) or WHERE branche_name = 'branch1' OR branche_name = 'branch2' for filtering the 2 branches that you need.

As for the malqaStore and tahliaStore, my guess is you wanted a condition sum on the quantity for each of the branche_name

CodePudding user response：

You need to add the branch name in your select and group by to sum data by each branch name.

Try something like..

SELECT invoice_date,
       branche_name,
       COUNT(quantity) AS malqaStore,
       COUNT(quantity) AS tahliaStore
FROM total_sales
WHERE branche_name = 'branch1'
  OR branche_name = 'branch2'         --<-- also you need an OR here not AND
GROUP BY invoice_date , branche_name;