How to get count based on Values in SQL

Question

I've something to execute I need the count based on values. Here is my Table

"ORD_NUM","ORD_AMOUNT","ORD_DATE","CUST_CODE","AGENT_CODE","ORD_DESCRIPTION"
"200118"|"500"|"07/20/2008"|"C00023"|"A006"|"SOD"
"200120"|"500"|"07/20/2008"|"C00009"|"A002"|"SOD"
"200129"|"1000"|"07/20/2008"|"C00024"|"A006"|"SOD"
"200127"|"1000"|"08/20/2008"|"C00015"|"A003"|"SOD"
"200128"|"500"|"08/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"500"|"09/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"1000"|"09/20/2008"|"C00009"|"A002"|"SOD"
"200128"|"1000"|"10/20/2008"|"C00009"|"A002"|"SOD"

In the order amount we only have either 1000 or 500. We need to return the data count of 500 and 1000 for each date. EX:

Date      |1000Count|500Count
07/20/2008|    1    |   2
08/20/2008|    1    |   1
09/20/2008|    1    |   1
10/20/2008|    1    |   1

Please don't tag conflicting products; SQL Server and MySQL are completely different products, and use very different dialects of SQL. I've removed the tags, you'll need to (re)tag the product you are really using. — Thom A
– Thom A ♦, Commented Jan 23, 2023 at 11:37
And please search for "pivot table" or "rows to columns" as this question has already been answered lots of times for both products here on SO. — Shadow
– Shadow, Commented Jan 23, 2023 at 11:38
@Chanikya this is why I suggested you to search SO. Pivoting is quite database specific. An SQl server solutionis unlikely to work in mysql, while mysql solution may work in ms sql server, but may not be efficient. — Shadow
– Shadow, Commented Jan 23, 2023 at 12:14
This is simple two times conditional count/sum with a GROUP BY date. — Jonas Metzler
– Jonas Metzler, Commented Jan 23, 2023 at 12:57

Guptha · Accepted Answer · 2023-01-24 10:01:24Z

2

Thanks! to SlavaRozhnev

select 
d,
count(case when amount = 500 then 1 end)    count500,
count(case when amount = 1000 then 1 end)   count1000
from test
group by d;

Gives Result :

+============+==========+===========+
| d          | count500 | count1000 |
+============+==========+===========+
| 2023-01-01 | 4        | 2         |
| 2023-01-02 | 1        | 1         |
| 2023-01-03 | 0        | 1         |
| 2023-01-04 | 1        | 0         |
+------------+----------+-----------+

edited Jan 24, 2023 at 10:01

Guptha

284 bronze badges

answered Jan 24, 2023 at 5:58

Chanikya

4961 gold badge8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get count based on Values in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related