CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
Charlie Ansell
Charlie Ansell

Reputation: 461

ignore certain columns in where clause sql

I have created a test DB to try and figure this out. I created a sales table which has an amount,sale_made (when the sale was made) and item description in the table.I'm trying to do it by only using one table only, I want to get the sum of all items sold overall, the number of items sold overall and half the total amount of days selling items. here is my query

select sum(amount) as 'total amount for all items sold'
,count(amount) as 'Total items sold',
 count(sale_made)as 'half total days' from sales where sale_made BETWEEN "2018-07-02" and "2018-07-05" ;

the query is working but the where clause is affecting the overall sum(amount) and the volume of items sold. is there a way I can keep the where clause in an not affect those first two columns?

i have tried self-joining the table and using table alias names but that doesn't make a difference

Upvotes: 1

Views: 312

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You want conditional aggregation:

select sum(amount) as `total amount for all items sold`,
       count(amount) as `Total items sold`,
       sum(sale_made between '2018-07-02' and '2018-07-05') as `half total days`
from sales;

Notes:

  • Single quotes are for string and date constants (ANSI-standard). That is why I change the double quotes to single quotes.
  • MySQL uses backticks to escape names. I don't like column aliases that need to be quoted, but you are using them, so I chose backticks (double quotes would also work).
  • MySQL has a convenient shorthand of treating booleans as integers in a numeric context, with "1" for true and "0" for false. Hence the use of sum() for the third column.

Upvotes: 3

Related Questions