September 13, 2002 at 6:12 am
I would like to count the number of rows under condition below
SELECT source_order_prefix, source_order_number, style_description FROM sum_orders
WHERE source_order_prefix LIKE '%K%' AND source_order_number LIKE '%9735%'
GROUP BY source_order_prefix, source_order_number, style_description
ORDER BY source_order_prefix, source_order_number
September 13, 2002 at 8:07 am
Count by group or overall? If you want a count by group just include Count(*) in your select. If you want a count of the number of rows resulting from the query, just treat your first query as a sub select, like this:
select count(*) from (your select here)
Andy
September 23, 2002 at 10:36 am
How do I count the total for each month and year for this query on Northwind database?
SELECT month(ShippedDate) AS Month, year(ShippedDate) AS Year, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
Group By month(ShippedDate), year(ShippedDate), Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
Order by month(ShippedDate), year(ShippedDate)
I would like to know the total count for each month and if no data is available for any month, then I would like to get a count of zero.
Can anyone help or provide some input on this.
Thanks
September 23, 2002 at 11:52 pm
Try this....
SELECT year(ShippedDate) AS Year, month(ShippedDate) AS Month, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
Order by year(ShippedDate) ,month(ShippedDate)
COMPUTE sum("Order Subtotals".Subtotal) by year(ShippedDate), month(ShippedDate)
Cheers.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 24, 2002 at 4:15 am
Like Andy said you just add COUNT(*) to get counts for each grouping.
SELECT source_order_prefix, source_order_number, style_description count(*) as ttl_grp_cnt FROM sum_orders
WHERE source_order_prefix LIKE '%K%' AND source_order_number LIKE '%9735%'
GROUP BY source_order_prefix, source_order_number, style_description
ORDER BY source_order_prefix, source_order_number
If you need subtotals and overall totals look at the optional WITH CUDE an WITH ROLLUP bits for GROUP BY.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply