multiple counts.. 1 table sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple counts.. 1 table sql

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-18 : 20:19:14

hey guys

this is the table i have (id is a primary key):

ID |TRANSNUMBER |STORE |DATE
1 |100 |10 |2011-01-01
2 |100 |11 |2011-01-01
3 |100 |11 |2011-01-01
4 |102 |12 |2011-01-01
5 |102 |13 |2011-01-01
6 |103 |14 |2011-01-01
7 |104 |10 |2011-01-01

So we can have the same transaction number for different stores. Every item that gets sold will have an Id even if its on the same transactionnumber.
I need to work out the total number of items sold at one store
and the total number of transactions using the one query.

the table is called inventory

i have worked out to determine the amount of items sold i need to count the total number of id's by the store
and
to calculate the total number of customer i need to count the total number of transactions per store.

however when i count the id and transaction number using simple count measures such as COUNT(id) and COUNT(transnumber)
it will return the same result.. which is wrong

SELECT
COUNT(TRANSNUMBER),
COUNT(ID),
STORE,
DATE
FROM INVENTORY

GROUP BY STORE,DATE

this returns the same result for both counted numbers...
do i need to write multiple sql queries into the one..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-18 : 20:26:57

[code]
SELECT
COUNT(DISTINCT TRANSNUMBER),
COUNT(ID),
STORE,
DATE
FROM INVENTORY

GROUP BY STORE,DATE
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-18 : 20:30:26

wow.. that was easy enough.. thanks again khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources