Okay, I have these two tables.
In table A, aID is unique index ID and cID is a customer ID. In Table B, gID is an index which relates to ID1 or ID2 from Table A. and AB is a 1 or 2 indicator.
>
Table A:
> aID | cID | DateTime | ID1 | ID2
>
>
Table B:
> aID | ItemCnt | gID | AB
I’m trying to conduct a count, from Table A. Showing the COUNT of Table A entries and SUM of ItemCnt.
Then grouping them via year and ordering them.
Here’s what I have but I’m getting weird numbers from my count.
“`
SELECT distinct YEAR(a.DateTime ), SUM(b.ItemCnt) as TTL, count(a.aID)
FROM `TableA` a
JOIN `TableB` b ON a.aID = b.aId
WHERE
CASE
WHEN a.ID1 = 9 THEN b.AB= 1
WHEN a.ID2 = 9 THEN b.AB= 2
END
GROUP BY
YEAR(a.DateTime)
HAVING SUM(b.ItemCnt) > 0
ORDER BY
SUM(b.ItemCnt) DESC, a.ItemCnt ASC
limit 10