SQL Sum Count

system · January 17, 2015, 7:26pm

I have the following query that works as expected. I now want to sum the counts grouped by community type in the same query and cant get that part figured out.

[php]SELECT
c.community_type,
lt.lot_type_description,
Count(lot.lot_type_id) AS count
FROM
community AS c
INNER JOIN block AS b ON b.community_id = c.community_id
INNER JOIN lot ON lot.block_id = b.block_id
INNER JOIN lot_type AS lt ON lot.lot_type_id = lt.lot_type_id
WHERE
lot.lot_type_id <> 1
GROUP BY
c.community_type,
lt.lot_type_description
[/php]

CURRENT RESULT

[table]
[tr]
[td]community_type[/td]
[td]lot_description[/td]
[td]count[/td]
[/tr]

[tr]
[td]1[/td]
[td]Dried In[/td]
[td]1[/td]
[/tr]
[tr]
[td]1[/td]
[td]Slab[/td]
[td]3[/td]
[/tr]
[tr]
[td]2[/td]
[td]Slab[/td]
[td]1[/td]
[/tr][/table]

Result desired is something like:

[table]
[tr]
[td]community_type[/td]
[td]lot_description[/td]
[td]count[/td]
[td]SUM[/td]
[/tr]

[tr]
[td]1[/td]
[td]Dried In[/td]
[td]1[/td]
[td]4[/td]
[/tr]
[tr]
[td]1[/td]
[td]Slab[/td]
[td]3[/td]
[td]4[/td]
[/tr]
[tr]
[td]2[/td]
[td]Slab[/td]
[td]1[/td]
[td]1[/td]
[/tr]
[/table]

Strider64 · January 17, 2015, 10:16pm

[php] SUM(lot.lot_type_id) as total_type[/php] Hmm??? That’s an interesting one…I am sure someone can figure it out.

benanamen · January 18, 2015, 6:05am

“SO IT HAS BEEN WRITTEN, SO IT SHALL BE DONE……”

Here is your answer: (See Attached Screen Shot)

[php]SELECT
c.community_type,
Sum(IF(lot.lot_type_id = ‘2’, 1, 0)) AS slab,
Sum(IF(lot.lot_type_id = ‘3’, 1, 0)) AS dried_in,
Count(lot.lot_type_id) AS total_slab_dried
FROM
community AS c
INNER JOIN block AS b ON b.community_id = c.community_id
INNER JOIN lot ON lot.block_id = b.block_id
INNER JOIN lot_type AS lt ON lot.lot_type_id = lt.lot_type_id
WHERE
lot.lot_type_id <> 1
GROUP BY
c.community_type[/php]