Group By & Get Count of 1 to many table

Question

I have a table that looks like this

CREATE TABLE `purchases` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `totalAmount` INT(10) NOT NULL DEFAULT '0',
    `purchaseDate` DATETIME NOT NULL,
    PRIMARY KEY (`id`)
)

and another table that stores all the details of the purchases (eg.line items)

CREATE TABLE `purchase_items` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `purchaseId` INT(10) NOT NULL,
    `itemId` INT(10) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`purchaseId`) REFERENCES purchases(id)
)

I get the total purchases on any given day this way

SELECT DATE_FORMAT(purchaseDate, '%m-%d-%Y') AS date,
COUNT(id) AS totalPurchases
FROM purchases
GROUP BY DATE(purchaseDate)

This gives me a result as such

date        totalPurchases
11-18-2010  5
11-19-2010  10
11-20-2010  10

How will I perform a query such that it returns me something like this

date        totalPurchases  totalItems
11-18-2010  5               20
11-19-2010  10              30
11-20-2010  10              25

So I will still be grouping by date but I need to perform a join on 'purchase_items' and get the total number of item that were linked to the

5 Purchases on 11-18-2010
10 Purchases on 11-19-2010
10 Purchases on 11-20-2010

score 2 · Accepted Answer · answered Nov 23 '10 at 05:44

2

Use:

  SELECT DATE_FORMAT(p.purchasedate, '%m-%d-%Y'),
         COUNT(p.id) AS totalPurchases,
         COUNT(pi.itemid) AS totalItems
    FROM PURCHASES p
    JOIN PURCHASE_ITEMS pi ON pi.purchaseid = p.id
GROUP BY DATE_FORMAT(p.purchasedate, '%m-%d-%Y')

If it's possible that there's no relation to the PURCHASE_ITEMS tables, use:

   SELECT DATE_FORMAT(p.purchasedate, '%m-%d-%Y'),
          COUNT(p.id) AS totalPurchases,
          COUNT(pi.itemid) AS totalItems
     FROM PURCHASES p
LEFT JOIN PURCHASE_ITEMS pi ON pi.purchaseid = p.id
 GROUP BY DATE_FORMAT(p.purchasedate, '%m-%d-%Y')

answered Nov 23 '10 at 05:44

OMG Ponies

325,700
82
523
502

Kidnly tell me, if there is any benifit if I use `cross apply` for first statement (see my answer)? – TheVillageIdiot Nov 23 '10 at 05:49
@TheVillageIdiot: MySQL (as the question is tagged) [doesn't support `CROSS APPLY`](http://dev.mysql.com/doc/refman/5.0/en/join.html) - that's TSQL/SQL Server syntax. Beyond that, your answer lies with this [SO question](http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join) and [explainextended article](http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/). – OMG Ponies Nov 23 '10 at 06:00

pavanred · Answer 2 · 2010-11-23T06:06:36.187

1

Edit -

SELECT DATE_FORMAT(A.purchaseDate, '%m-%d-%Y') AS date,
COUNT(A.id) AS totalPurchases,count(B.id)
FROM purchases A LEFT OUTER JOIN purchaseitems B on A.id = B.purchaseid
GROUP BY DATE(purchaseDate)

edited Nov 23 '10 at 06:06

answered Nov 23 '10 at 05:45

pavanred

12,717
14
53
59

Somehow inner join does not work if some purchases don't have items. I know it's ridiculous that purchases would not have items, but this was just an example I came up with. My original 1-to-many table could actually not have any line items. Thanks though! – Girish Dusane Nov 23 '10 at 06:04
Oh that should be fine, you can use a left outer join instead of an inner join. Will update my answer in a sec. – pavanred Nov 23 '10 at 06:05

score 0 · Answer 3 · edited Aug 18 '12 at 01:36

0

Try this:

select p.purchasedate, sum(p.id), items.items_count
from purchases p
cross apply
(
   select count(id) [items_count]
   from purchase_items
   where purchaseId = p.id
)items
group by p.purchasedate, items.items_count

edited Aug 18 '12 at 01:36

Conner

30,144
8
52
73

answered Nov 23 '10 at 05:47

TheVillageIdiot

40,053
20
133
188

I haven't tried this as the whole cross apply thing seems a bit too complicated for me at the moment. Thanks though, definitely something I might want to try out in the future. – Girish Dusane Nov 23 '10 at 06:05

Group By & Get Count of 1 to many table

3 Answers3